﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace ManagementProject
{
    public partial class frmOrderDetails : Form
    {
        int orderid, oldproductid;
        SqlConnection con = new SqlConnection();
        SqlCommand cmd;

        public frmOrderDetails()
        {
        }

        public frmOrderDetails(int orderid)
        {
            this.orderid = orderid;
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            InitializeComponent();
            LoadOrderDetails();
            LoadProductComboBox();
        }

        #region MainFunction
        void LoadOrderDetails()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getOrderDetails";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = orderid;
                cmd.Parameters.Add(param);

                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrderDetails.Rows.Clear();
                while (dr.Read())
                {
                    dgvOrderDetails.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                            dr[3].ToString(), dr[4].ToString());
                }
                dr.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Cannot connect to database!");
            }
            finally
            {
                con.Close();
            }
        }

        void AddNewData()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertOrderDetails";

                SqlParameter param;
                param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = orderid;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = cmbProduct.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = txtUnitPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@qty", SqlDbType.SmallInt);
                param.Value = short.Parse(txtQuantity.Text);
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discount", SqlDbType.Decimal);
                param.Value = decimal.Parse(txtDiscount.Text);
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                MessageBox.Show("Duplicated product!");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Cannot add data!");
            }
            finally
            {
                con.Close();
                ClearData();
            }
        }

        private void LoadProductComboBox()
        {
            try
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                SqlDataAdapter da = new SqlDataAdapter("SELECT productid, productname FROM Production.Products", con);
                DataTable dt = new DataTable("Products");
                da.Fill(dt);

                cmbProduct.DataSource = dt;
                cmbProduct.DisplayMember = "productname";
                cmbProduct.ValueMember = "productid";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Cannot connect to database!");
            }
        }

        private bool ValidateInput()
        {
            bool isValid = true;
            errorProvider1.Clear();

            if (txtUnitPrice.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtUnitPrice, "Please input Unit Price");
                isValid = false;
            }
            else if (!IsPositiveDecimal(txtUnitPrice.Text))
            {
                errorProvider1.SetError(txtUnitPrice, "Must be positive number");
                isValid = false;
            }

            if (txtQuantity.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtQuantity, "Please input Quantity");
                isValid = false;
            }
            else if (!IsPositiveInteger(txtQuantity.Text))
            {
                errorProvider1.SetError(txtQuantity, "Must be positive number");
                isValid = false;
            }

            if (txtDiscount.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtDiscount, "Please input Discount");
                isValid = false;
            }
            else
            {
                decimal tmp;
                try
                {
                    tmp = decimal.Parse(txtDiscount.Text.Trim());
                    if (tmp < 0 || tmp > 1)
                    {
                        errorProvider1.SetError(txtDiscount, "Must be between 0 and 1");
                        isValid = false;
                    }
                }
                catch (System.Exception ex)
                {
                    errorProvider1.SetError(txtDiscount, "Must be number");
                    isValid = false;
                }
            }

            return isValid;
        }

        private void UpdateData()
        {
            if (dgvOrderDetails.SelectedRows.Count > 0)
            {
                try
                {
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "UpdateOrderDetails";

                    SqlParameter param;
                    param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = orderid;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@oldproductid", SqlDbType.Int);
                    param.Value = oldproductid;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@newproductid", SqlDbType.Int);
                    param.Value = cmbProduct.SelectedValue;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@unitprice", SqlDbType.Money);
                    param.Value = txtUnitPrice.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@qty", SqlDbType.SmallInt);
                    param.Value = short.Parse(txtQuantity.Text);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@discount", SqlDbType.Decimal);
                    param.Value = decimal.Parse(txtDiscount.Text);
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                }
                catch (SqlException ex)
                {
                    MessageBox.Show("Duplicated product!");
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Cannot update data!");
                }
                finally
                {
                    con.Close();
                    ClearData();
                }
            }
        }

        private void DeleteData()
        {
            if (dgvOrderDetails.SelectedRows.Count > 0)
            {
                try
                {
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "DeleteOrderDetails";

                    SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
                    param.Value = cmbProduct.SelectedValue;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Cannot delete data");
                }
                finally
                {
                    con.Close();
                    ClearData();
                }
            }
        }

        private void ClearData()
        {
            txtUnitPrice.Text = "";
            txtQuantity.Text = "";
            txtDiscount.Text = "";
            btnAdd.Enabled = true;
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            LoadOrderDetails();
        }
        #endregion


        #region ControlEvent
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                UpdateData();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DeleteData();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            ClearData();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                AddNewData();
            }
        }

        private void dgvOrderDetails_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvOrderDetails.SelectedRows.Count > 0)
            {
                try
                {
                    btnUpdate.Enabled = true;
                    btnDelete.Enabled = true;
                    btnAdd.Enabled = false;
                    DataGridViewRow r = dgvOrderDetails.SelectedRows[0];
                    cmbProduct.SelectedValue = r.Cells[1].Value.ToString();
                    txtUnitPrice.Text = r.Cells[2].Value.ToString();
                    txtQuantity.Text = r.Cells[3].Value.ToString();
                    txtDiscount.Text = r.Cells[4].Value.ToString();
                    oldproductid = int.Parse(cmbProduct.SelectedValue.ToString().Trim());
                }
                catch (System.Exception ex)
                {

                }
            }
        }
        #endregion


        #region Utilities
        private bool IsPositiveDecimal(string s)
        {
            Decimal tmp;
            return Decimal.TryParse(s, out tmp) && tmp > 0;
        }

        private bool IsPositiveInteger(string s)
        {
            int tmp;
            return int.TryParse(s, out tmp) && tmp > 0;
        }
        #endregion
    }
}